set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;
insert overwrite table jms_dm.dm_cn_export_diff_weight_sum_dt partition (dt = '{{ execution_date | cst_ds }}')
select scan_date
     , pick_network_code
     , max(pick_network_name)
     , max(subordinate_agent_code)
     , max(subordinate_agent_name)
     , max(franchisee_code)
     , max(franchisee_name)
     , center_code
     , max(center_name)
     , sum(if(center_weightornot = 1,1,0))                                                                         as weight_qty
     , sum(case when center_weightornot = 0 then 0 when compare_weight <> center_weight then 1 else 0 end)           as diff1_qty
     , cast(sum(case when center_weightornot = 0 then 0
                    when compare_weight <> center_weight then abs(center_diff_weight)
                    else 0 end) as decimal(13, 2))                                                                   as diff1_wty
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 0 and abs(center_diff_weight) <= 0.5 then 1
               else 0 end)                                                                                           as zero_half_1
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 0.5 and abs(center_diff_weight) <= 1 then 1
               else 0 end)                                                                                           as half_one_1
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 1 and abs(center_diff_weight) <= 3 then 1
               else 0 end)                                                                                           as one_three_1
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 3 and abs(center_diff_weight) <= 5 then 1
               else 0 end)                                                                                           as three_five_1
     , sum(case when center_weightornot = 0 then 0 when abs(center_diff_weight) > 5 then 1 else 0 end)               as than_five_1
     , sum(case when center_weightornot = 0 then 0
               when (center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                    (center_weight < 10 and (abs(center_diff_weight)) > 0.5) then 1
               else 0 end)                                                                                           as diff2_qty
     , cast(sum(case when center_weightornot = 0 then 0
                    when (center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                         (center_weight < 10 and (abs(center_diff_weight)) > 0.5) then abs(center_diff_weight)
                    else 0 end) as decimal(13, 2))                                                                   as diff2_wty
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 0 and
                    abs(center_diff_weight) <= 0.5 then 1
               else 0 end)                                                                                           as zero_half_2
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and
                    abs(center_diff_weight) > 0.5 and abs(center_diff_weight) <= 1 then 1
               else 0 end)                                                                                           as half_one_2
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 1 and
                    abs(center_diff_weight) <= 3 then 1
               else 0 end)                                                                                           as one_three_2
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 3 and
                    abs(center_diff_weight) <= 5 then 1
               else 0 end)                                                                                           as three_five_2
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 5
                   then 1
               else 0 end)                                                                                           as than_five_2
     , sum(case when center_weightornot = 0 then 0 when center_weight > compare_weight then 1 else 0 end)                                               as diff3_qty
     , cast(sum(case when center_weightornot = 0 then 0 when center_weight > compare_weight then abs(center_diff_weight) else 0 end) as decimal(13, 2)) as diff3_wty
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 0 and abs(center_diff_weight) <= 0.5 and center_weight > compare_weight
                   then 1
               else 0 end)                                                                                           as zero_half_3
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 0.5 and abs(center_diff_weight) <= 1 and center_weight > compare_weight
                   then 1
               else 0 end)                                                                                           as half_one_3
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 1 and abs(center_diff_weight) <= 3 and center_weight > compare_weight then 1
               else 0 end)                                                                                           as one_three_3
     , sum(case when center_weightornot = 0 then 0
               when abs(center_diff_weight) > 3 and abs(center_diff_weight) <= 5 and center_weight > compare_weight then 1
               else 0 end)                                                                                           as three_five_3
     , sum(case when center_weightornot = 0 then 0 when abs(center_diff_weight) > 5 and center_weight > compare_weight then 1 else 0 end)               as than_five_3
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and center_weight > compare_weight then 1
               else 0 end)                                                                                           as diff4_qty
     , cast(sum(case when center_weightornot = 0 then 0
                    when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                          (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and center_weight > compare_weight
                        then abs(center_diff_weight)
                    else 0 end) as decimal(13, 2))                                                                   as diff4_wty
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 0 and
                    abs(center_diff_weight) <= 0.5 and center_weight > compare_weight then 1
               else 0 end)                                                                                           as zero_half_4
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and
                    abs(center_diff_weight) > 0.5 and abs(center_diff_weight) <= 1 and center_weight > compare_weight
                   then 1
               else 0 end)                                                                                           as half_one_4
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 1 and
                    abs(center_diff_weight) <= 3 and center_weight > compare_weight then 1
               else 0 end)                                                                                           as one_three_4
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 3 and
                    abs(center_diff_weight) <= 5 and center_weight > compare_weight then 1
               else 0 end)                                                                                           as three_five_4
     , sum(case when center_weightornot = 0 then 0
               when ((center_weight >= 10 and (abs(center_diff_weight) / center_weight) > 0.05) or
                     (center_weight < 10 and (abs(center_diff_weight)) > 0.5)) and abs(center_diff_weight) > 5 and
                    center_weight > compare_weight then 1
               else 0 end)                                                                                           as than_five_4
     , count(1)                                                       as     weight_ot
from jms_dm.dm_cn_export_diff_weight_detail_dt
where dt = '{{ execution_date | cst_ds }}'
group by scan_date
       , pick_network_code
       , center_code
DISTRIBUTE BY 1 ;